﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using DimensionsHelper.Common.Contracts.Excel;
using DimensionsHelper.Common.Contracts.Sql;
using DimensionsHelper.Common.Utils;
// ReSharper disable RedundantSuppressNullableWarningExpression

namespace DimensionsHelper.Common.Sql;

/// <summary>
///     码表元素列表，此列表中元素允许多个字段使用同一个列表。
/// </summary>
[Serializable]
public class MetadataCodeMap
{
    private readonly Dictionary<string, string> _fieldMapping;

    private readonly HashSet<string> _ignoreFields = [];
    private IList<MetadataCodeMapElement> _codeMapping;

    private bool _isDirty;
    private IList<string> _noLabelCodesStorage;
    private IList<string> _singleCodeMapping;

    public MetadataCodeMap()
    {
        // 不添加前缀标签的码号正则表达式
        _noLabelCodesRegex = new RegexManager { Exactly = true, IgnoreCase = true };
        _noLabelCodesRegex.FormatElement += OnNoLabelCodesRegexElementFormatting;
        // 跳过字段名正则表达式集合
        _ignoreFieldRegex = new RegexManager { Exactly = true, IgnoreCase = true };
        _ignoreFieldRegex.FormatElement += OnFieldMappingRegexElementFormatting;
        // 字段映射正则表达式集合
        _fieldMappingRegex = new RegexManager { Exactly = true, IgnoreCase = true };
        _fieldMappingRegex.FormatElement += OnFieldMappingRegexElementFormatting;
        // 值正则表达式
        _valueRegex = new RegexManager { Exactly = true, IgnoreCase = true };
        _valueRegex.FormatElement += OnRegexElementFormatting;
        // 单值码号映射正则表达式
        _singleCodeMappingRegex = new RegexManager { Exactly = true, IgnoreCase = true };
        _singleCodeMappingRegex.FormatElement += OnFieldMappingRegexElementFormatting;

        _isDirty = true;

        _singleCodeMapping = new List<string>();
        _codeMapping = new List<MetadataCodeMapElement>();
        _fieldMapping = new Dictionary<string, string>();
        _noLabelCodesStorage = new List<string>();

        StartRow = 2;
    }

    /// <summary>
    ///     读取电子表格时的起始行，行号小于此行的视为字段名区域。
    /// </summary>
    public int StartRow { get; set; }

    /// <summary>
    ///     全局码号标签，此字符串会自动追加到所有码号前面。
    /// </summary>
    public string Label { get; set; } = "_";

    /// <summary>
    ///     二级字段时下级字段名。
    /// </summary>
    public string SubFieldName { get; set; } = "Slice";

    /// <summary>
    ///     此属性为多个数据中间的分隔符，如果单元格内是单一数据，设置此值为<see cref="string.Empty" />。
    /// </summary>
    public string MultipleSplitter { get; set; } = ",";

    /// <summary>
    ///     获取或设置不添加<see cref="Label" />的码号名，不区分大小写。
    /// </summary>
    /// <remarks>
    ///     注意，手动创建对象赋值时，不要使用此属性返回值的List对象添加，必须要将存有数据的IList对象赋值给此属性。
    /// </remarks>
    public IList<string> NoLabelCodes
    {
        get => _noLabelCodesStorage;

        set
        {
            _noLabelCodesRegex.Clear();

            foreach (string item in value)
            {
                _noLabelCodesRegex.Append(item);
            }

            _noLabelCodesStorage = value;
            _isDirty = true;
        }
    }

    /// <summary>
    ///     单值字段列不为空白的值的映射码号。
    /// </summary>
    public string SingleCodeReflect { get; set; } = "1";

    /// <summary>
    ///     获取或配置单值字段值映射表，此列表内的字段名视为单值字段。
    ///     此列表中可以包含正则表达式（包含?），成功匹配到的字符将作为单值码号。
    ///     此属性优先级高于<see cref="SingleCodeReflect" />
    /// </summary>
    /// <remarks>
    ///     注意，手动创建对象赋值时，不要使用此属性返回值的List对象添加，必须要将存有数据的IList对象赋值给此属性。
    /// </remarks>
    public IList<string> SingleCodeMapping
    {
        get => _singleCodeMapping;

        set
        {
            _singleCodeMapping = value;
            _isDirty = true;
        }
    }


    /// <summary>
    ///     获取或设置忽略的字段，此集合中的字段将不会在生成的SQL脚本中出现。
    /// </summary>
    /// <remarks>
    ///     注意，手动创建对象赋值时，不要使用此属性返回值的List对象添加，必须要将存有数据的IList对象赋值给此属性。
    /// </remarks>
    public IList<string> IgnoreFields
    {
        get => _ignoreFields.ToList();

        set
        {
            _ignoreFields.Clear();

            foreach (string f in value)
            {
                _ignoreFields.Add(f.ToLower().Trim());
            }
        }
    }


    /// <summary>
    ///     获取或设置字段映射表，如果字段设置了此值，生成的SQL脚本将使用此表中的对应名称。
    /// </summary>
    public IDictionary<string, string> FieldMapping
    {
        get => _fieldMapping;

        set
        {
            _fieldMapping.Clear();

            foreach (KeyValuePair<string, string> item in value)
            {
                _fieldMapping.TryAdd(item.Key.ToLower(), item.Value);
            }

            _isDirty = true;
        }
    }

    /// <summary>
    ///     样本关键字字段名，此处是映射前的原始名称。
    /// </summary>
    public string KeyField { get; set; } = string.Empty;

    /// <summary>
    ///     当前元素列表。
    /// </summary>
    public IList<MetadataCodeMapElement> CodeMapping
    {
        get => _codeMapping;

        set
        {
            _codeMapping = value;
            _isDirty = true;
        }
    }

    /// <summary>
    ///     需要额外添加的字段和字段值。
    /// </summary>
    public IList<MetadataSqlAppendField> AppendFields { get; set; } = new List<MetadataSqlAppendField>();

    /// <summary>
    ///     尝试依据给定的字段名查找<see cref="SingleCodeMapping" />中的映射值
    /// </summary>
    /// <param name="fieldName">给定字段名</param>
    /// <param name="singleCode">查找到的最终码号</param>
    /// <returns>如果查找成功，返回true，否则，返回false</returns>
    public bool TryGetSingleCode(string fieldName, out string singleCode)
    {
        Refresh();

        singleCode = null!;

        var groups = _singleCodeMappingRegex.SearchGroups(fieldName);

        if (groups.Count == 0)
        {
            return false;
        }

        var mappedGroup = groups.Find(g => 
            g.GroupName.StartsWith("I") && g.GroupName.EndsWith("C"));

        if (mappedGroup == null)
        {
            return false;
        }

        singleCode = groups[1].GroupObject.Value;
        return true;
    }

    /// <summary>
    ///     获取检查<see cref="NoLabelCodes" />后添加<see cref="Label" />的最终码号字符串。
    /// </summary>
    /// <param name="code">需要检查的码号</param>
    /// <param name="appendCurlyParen">是否在最终结果外添加花括号</param>
    /// <returns>检查<see cref="NoLabelCodes" />集合并添加描述的字符串。</returns>
    public string GetCode(string code, bool appendCurlyParen = true)
    {
        if (!(string.IsNullOrEmpty(code) ||
              (_noLabelCodesRegex.Count > 0 && _noLabelCodesRegex.Match(code).Success) /* 需要排除空正则表达式 */))
        {
            code = Label + code.Replace(" ", "").Replace(",", "," + Label);
        }

        return appendCurlyParen ? "{" + code + "}" : code;
    }

    /// <summary>
    ///     判断给定字段名是否在当前设置的单值字段列表中。
    /// </summary>
    /// <param name="fieldName">给定字段名，不区分大小写。</param>
    /// <returns>如果给定字段在当前单值字段表中，返回true，否则，返回false</returns>
    public bool IsSingleCodeField(string fieldName)
    {
        return _singleCodeMappingRegex.Count > 0 && _singleCodeMappingRegex.Match(fieldName).Success;
    }


    /// <summary>
    ///     判断给定字段名是否在当前设置的忽略字段列表中。
    /// </summary>
    /// <param name="fieldName">给定字段名，不区分大小写。</param>
    /// <returns>如果给定字段应该被忽略，返回true，否则，返回false</returns>
    public bool IsFieldIgnored(string fieldName)
    {
        return _ignoreFieldRegex.Count > 0 && _ignoreFieldRegex.Match(fieldName.Trim()).Success;
    }

    /// <summary>
    ///     判断给定字段是否配置了映射名称。
    /// </summary>
    /// <param name="fieldName">给定字段名，不区分大小写。</param>
    /// <returns>如果给定字段配置在映射表中，返回true，否则，返回false</returns>
    public bool IsFieldMapping(string fieldName)
    {
        return _fieldMappingRegex.Count > 0 && _fieldMappingRegex.Match(fieldName).Success;
    }

    /// <summary>
    ///     检查字段映射后，获取最终的字段名。
    /// </summary>
    /// <param name="name">当前的字段名</param>
    /// <param name="replaceValue">被替换掉的字符串</param>
    /// <returns>最终的字段名</returns>
    public string GetField(string name, out string replaceValue)
    {
        replaceValue = string.Empty;

        Refresh();

        string fieldName = GetFieldMappingKey(name, out var replaceGroup);

        // 如果查找到空字符串，返回原始名称。
        string final = name;

        if (_fieldMapping.TryGetValue(fieldName.ToLower(), out var find) && 
            !string.IsNullOrEmpty(find))
        {
            final = find;
        }

        // 替换 ?
        if (replaceGroup != null)
        {
            final = final.Replace("?", replaceGroup.Value);
            replaceValue = replaceGroup.Value;
        }

        return final;
    }

    /// <summary>
    ///     尝试查找包含指定字段名的码表定义元素。
    /// </summary>
    /// <param name="fieldName">查找的字段名，不区分大小写</param>
    /// <param name="element">查找到的已有定义元素，如果未找到，此值为null</param>
    /// <returns>如果成功查找到，返回true，否则，返回false</returns>
    public bool TryGetElement(string fieldName, out MetadataCodeMapElement element)
    {
        element = null!;
        foreach (MetadataCodeMapElement item in CodeMapping)
        {
            if (!item.FieldExists(fieldName))
            {
                continue;
            }

            element = item;
            return true;
        }
        return false;
    }

    /// <summary>
    ///     依据给定值查找包含此值元素。
    /// </summary>
    /// <param name="value">需要查找的值</param>
    /// <param name="element">已有的元素</param>
    /// <returns>如果成功查找到，返回true，否则，返回false</returns>
    public bool TryGetElementByValue(string value, out MetadataCodeMapElement element)
    {
        Refresh();

        element = null!;

        int index = _valueRegex.SearchIndex(value);

        if (index == -1)
        {
            return false;
        }

        element = CodeMapping[index];

        return true;
    }

    private void OnElementValueChanged(string current, string insertValue)
    {
        _valueRegex.Append(insertValue, _valueRegex.FindOriginalIndex(current));
    }

    private void ReadExcelSheet(IExcelWorksheetOperator worksheet)
    {
        int maxRow = worksheet.EndRow;
        int maxCol = worksheet.EndColumn;

        if (maxRow <= 1 || maxCol <= 0)
        {
            return;
        }

        // 需要检查多行字段 
        // [Field] [Empty] [Empty] ...
        // [Sub1 ] [Sub2 ] [Sub3 ] ...
        // [Sub11]   ...
        //   ...

        MetadataCodeMapElement? leveledField = null;

        for (int c = 1; c <= maxCol; c++)
        {
            string fieldName = worksheet.GetCellText(1, c).ToLower();
            string nextRowCell = worksheet.GetCellText(2, c);

            bool firstRowEmpty = string.IsNullOrEmpty(fieldName);
            bool nextRowEmpty = string.IsNullOrEmpty(nextRowCell);

            bool isSubField = false;

            // [Field] [Empty]
            // [Sub1 ]   ...
            if (!firstRowEmpty && !nextRowEmpty &&
                c < maxCol &&
                string.IsNullOrEmpty(worksheet.GetCellText(1, c + 1)))
            {
                isSubField = true;
            }
            // [Empty]
            // [Sub  ]
            else if (firstRowEmpty && !nextRowEmpty)
            {
                isSubField = true;
            }
            // 其他情况，视作空列
            else if (firstRowEmpty)
            {
                continue;
            }

            var values = new HashSet<string>();

            bool isNumberField = true;
            bool isDateField = true;

            for (int r = isSubField ? 3 : 2; r <= maxRow; r++)
            {
                string cellText = worksheet.GetCellText(r, c);

                // 忽略空白和错误字符串
                if (string.IsNullOrEmpty(cellText) || StringHelper.IsExcelError(cellText))
                {
                    continue;
                }

                if (isNumberField && !double.TryParse(cellText, out _))
                {
                    isNumberField = false;
                }

                if (isDateField && !DateTime.TryParse(cellText, out _))
                {
                    isDateField = false;
                }

                values.Add(cellText!);
            }

            if (isSubField)
            {
                // 如果第一行为空，添加进leveledField中，如果leveledField为null，跳过。
                if (firstRowEmpty && leveledField == null)
                {
                    continue;
                }

                // 如果 leveledField 为 null，用 fieldName 创建一个新的
                if (leveledField == null)
                {
                    leveledField = MetadataCodeMapElement.FromArray(this,
                        [fieldName], 
                        values.ToArray());
                    UpdateElementRegex(leveledField);
                    CodeMapping.Add(leveledField);
                }

                // 添加 nextRowCell 进 leveledField 的 SubFieldsMapping 表中
                leveledField.SubFieldsMapping.SetValue(nextRowCell.ToLower(),
                    "_" + leveledField.SubFieldsMapping.Count);

                continue;
            }

            // 跳过纯数值、日期列、空白列
            if (isNumberField || isDateField || values.Count == 0)
            {
                continue;
            }

            // 单值字段
            if (values.Count == 1)
            {
                SingleCodeMapping.Add(fieldName);
                continue;
            }

            MetadataCodeMapElement? element = null;

            foreach (string item in values)
            {
                if (TryGetElementByValue(item, out element))
                {
                    break;
                }
            }

            // 对于已有字段，检查值重合情况。如果新值完全覆盖了已有字段的值列表，应当合并进已有字段的 Fields 列表。
            if (element != null &&
                EnumerableHelper.IsSetCoincidence(element.Mapping.Keys.ToHashSet(), values,
                    out var difference))
            {
                element.AddField(fieldName);

                if (element.Mapping.Count >= values.Count)
                {
                    continue;
                }

                OnElementValueChanged(
                    string.Join(",", element.Mapping.Keys), 
                    string.Join(",", values));
                foreach (string item in difference)
                {
                    element.Mapping.Add(item, (element.Mapping.Count + 1).ToString());
                }
            }
            // 其他情况，新建字段。
            else
            {
                element = MetadataCodeMapElement.FromArray(this,
                    [fieldName],
                    values.ToArray());
                UpdateElementRegex(element);
                CodeMapping.Add(element);
            }
        }

        _isDirty = true;
    }

    public static MetadataCodeMap FromExcelSheet(IExcelWorksheetOperator worksheet)
    {
        var map = new MetadataCodeMap();
        map.ReadExcelSheet(worksheet);
        return map;
    }

    public static MetadataCodeMap FromExcelWorkbook(IExcelWorkbookOperator workbook)
    {
        var map = new MetadataCodeMap();

        foreach (IExcelWorksheetOperator sheet in workbook.Worksheets)
        {
            map.ReadExcelSheet(sheet);
        }

        return map;
    }

    #region Regex

    private readonly RegexManager _noLabelCodesRegex;
    private readonly RegexManager _ignoreFieldRegex;
    private readonly RegexManager _valueRegex;
    private readonly RegexManager _fieldMappingRegex;
    private readonly RegexManager _singleCodeMappingRegex;


    private static void OnNoLabelCodesRegexElementFormatting(object? sender, RegexElementFormatArgs args)
    {
        args.Text = StringHelper.EscapeRegExprExceptStar(args.Text).Replace("*", ".*?");
        args.Handled = true;
    }

    private static void OnFieldMappingRegexElementFormatting(object? sender, RegexElementFormatArgs args)
    {
        if (sender is not RegexManager regex)
        {
            return;
        }

        args.Text = $"(?<I{regex.Count}>^" + StringHelper.EscapeRegExprExceptStarOrQuestion(args.Text)
            .Replace("?", $"(?<I{regex.Count}N>[0-9]+)")
            .Replace("*", $"(?<I{regex.Count}C>[0-9]+)") + "$)";
        args.Handled = true;
    }

    private void OnRegexElementFormatting(object? sender, RegexElementFormatArgs args)
    {
        if (args.Texts.Count > 0)
        {
            args.Text = string.Join("|", args.Texts.Select(StringHelper.EscapeRegExprExceptDotStarQuestion));
        }
        else
        {
            args.Text = StringHelper.EscapeRegExprExceptDotStarQuestion(args.Text);
        }

        args.Text =
            $"(?:(?:{(string.IsNullOrEmpty(MultipleSplitter) ? "^" : $"^|(?<={MultipleSplitter})")})({args.Text})(?:{(string.IsNullOrEmpty(MultipleSplitter) ? "$" : $"$|(?={MultipleSplitter})")}))";
        args.Handled = true;
    }

    private void UpdateElementRegex(MetadataCodeMapElement element)
    {
        _valueRegex.Append(element.Mapping.Keys.ToArray());
    }

    /// <summary>
    ///     当正则表达式元素发生变化时，调用此方法可以刷新用于文本匹配的正则表达式。
    /// </summary>
    public void Refresh()
    {
        if (!_isDirty)
        {
            return;
        }

        _valueRegex.Clear();
        _valueRegex.Splitter = MultipleSplitter;

        if (StartRow < 2)
        {
            StartRow = 2;
        }

        foreach (MetadataCodeMapElement element in CodeMapping)
        {
            // 忽略没有配置 Fields 的字段
            if (element.Fields.Count == 0)
            {
                continue;
            }

            element.CodeMap = this;
            UpdateElementRegex(element);

            // 配置有效值最小是2，如果配置无效，将替换为3，如果配置有效，保持当前配置。
            if (element.SubFieldsMapping.Count > 0 && StartRow <= 2)
            {
                StartRow = 3;
            }
        }

        _fieldMappingRegex.Clear();

        foreach (string item in FieldMapping.Keys)
        {
            _fieldMappingRegex.Append(item);
        }

        _singleCodeMappingRegex.Clear();

        foreach (string item in SingleCodeMapping)
        {
            _singleCodeMappingRegex.Append(item);
        }

        _ignoreFieldRegex.Clear();

        foreach (string item in IgnoreFields)
        {
            _ignoreFieldRegex.Append(item);
        }

        _isDirty = false;
    }

    private string GetFieldMappingKey(string fieldName, out Group? replaceGroup)
    {
        replaceGroup = null;
        var groups = _fieldMappingRegex.SearchGroups(fieldName);

        string? normalGroup = null;
            
        foreach (var group in groups.Where(group => group.GroupName.Length > 1 && group.GroupName.StartsWith("I")))
        {
            if (group.GroupName.EndsWith("N"))
            {
                replaceGroup = group.GroupObject;
            }
            else if (!group.GroupName.EndsWith("C"))
            {
                normalGroup = group.GroupName;
            }
        }

        return !string.IsNullOrEmpty(normalGroup)
            ? _fieldMappingRegex.GetOriginalAt(int.Parse(normalGroup!.Substring(1)))
            : fieldName;
    }

    #endregion


    #region Update Settings

    private readonly RegexManager _columnsRegex = new() { Exactly = true, IgnoreCase = true };
    private readonly Dictionary<string, IList<string>> _updateColumnsMap = new(StringComparer.OrdinalIgnoreCase);
    private bool _mergeUpdateRecord;

    private void RefreshUpdateColumnsRegex()
    {
        _columnsRegex.Clear();
        _columnsRegex.FormatElement += ColumnsRegexFormatting;
        foreach (KeyValuePair<string, IList<string>> item in _updateColumnsMap)
        {
            if (Enum.TryParse<UpdateColumnKinds>(item.Key, true, out _))
            {
                _columnsRegex.Append(item.Value, item.Key);
            }
        }

        _columnsRegex.FormatElement -= ColumnsRegexFormatting;
    }
        
    private static void ColumnsRegexFormatting(object? sender, RegexElementFormatArgs e)
    {
        // * 占位符同正则表达式中的 .*
        e.Text = e.Text == "*" ? ".*" : StringHelper.EscapeRegExprExceptStar(e.Text).Replace("*", ".*?");
        e.Handled = true;
    }

    /// <summary>
    ///     在构建UPDATE脚本时，Excel首行内容对应的列类型。不要在外部使用IDictionary的修改方法更改其中的内容。
    /// </summary>
    public IDictionary<string, IList<string>> UpdateColumnsKind
    {
        get => _updateColumnsMap;
        set
        {
            _updateColumnsMap.Clear();

            foreach (KeyValuePair<string, IList<string>> pair in value)
            {
                _updateColumnsMap.Add(pair.Key.ToLower(), pair.Value);
            }

            RefreshUpdateColumnsRegex();
        }
    }


    /// <summary>
    ///     尝试获取指定列名的列类型，不区分大小写。
    /// </summary>
    /// <param name="columnName">待查找的列名</param>
    /// <param name="kind">查找到的列类型</param>
    /// <returns>如果成功查找到，返回true，否则，返回false。</returns>
    public bool TryGetUpdateColumnKind(string columnName, out UpdateColumnKinds kind)
    {
        kind = UpdateColumnKinds.Extra;

        var groups = _columnsRegex.SearchGroups(columnName);
        foreach (var g in groups)
        {
            if (!string.IsNullOrEmpty(g.GroupName) && Enum.TryParse(g.GroupName, true, out kind))
            {
                return true;
            }
        }

        return false;
    }


    /// <summary>
    ///     对Categorical分类值进行增删时使用的语句类型。
    /// </summary>
    public CategoricalSqlUpdateType CategoricalSqlType { get; set; } = CategoricalSqlUpdateType.UnionDifference;


    /// <summary>
    ///     对Categorical分类值计算分类列表时是否自动合并同一字段的多个值。
    /// </summary>
    public bool MergeUpdateValues { get; set; }

    /// <summary>
    ///     是否合并UPDATE脚本单一Record的多个字段值更新脚本。
    /// </summary>
    public bool MergeUpdateRecord
    {
        get => _mergeUpdateRecord;
        set => _mergeUpdateRecord = value;
    }

    #endregion
}